﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace DataAccess
{
    public class TableDAO
    {
        public const int TYPE_ALL = 0;
        public const int TYPE_DT = 1;
        public const int TYPE_PTBX = 2;
        public const int TYPE_GJBX = 3;
        public const int TYPE_VIPBX = 4;

        // 程序里的状态
        public const int STATE_ALL = 0;
        public const int STATE_AVAILABLE = 1;
        public const int STATE_ORDERING = 2;
        public const int STATE_ORDERED = 3;
        public const int STATE_PAID = 4;

        /**
         * Get total number of table count.
         */
        public int getTableMount()
        {
            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();
                string strSql = "select count( DISTINCT id) from tb_table";
                SqlCommand command = new SqlCommand(strSql, con);
                SqlDataReader sdr = command.ExecuteReader(CommandBehavior.CloseConnection);
                if (sdr.Read())
                {
                    return Int32.Parse(sdr[0].ToString());
                }
                return 0;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        public Object getTable(string strTableName)
        {
            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();

                // create a sql command object
                string strSql = String.Format(
                                                "SELECT tableName,tableType, tableState, tablePrice, notes"+
                                                " FROM tb_table WHERE tableName='{0}'", strTableName);
                SqlCommand cmd = new SqlCommand(strSql, con);
                SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                Hashtable ht = new Hashtable();
                if (sdr.Read())
                {
                    ht.Add("tableName", sdr["tableName"]);
                    ht.Add("tableType", sdr["tableType"]);
                    ht.Add("tableState", sdr["tableState"]);
                    ht.Add("tablePrice", sdr["tablePrice"]);
                    ht.Add("notes", sdr["notes"]);
                }
                return ht;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        /**
         * Get tables information.
         * @param type {int}: Type of tables to get. (0: All, 1: 大厅, 2: 普通包厢, 3: 高级包厢, 4: VIP包厢)
         * @param state {int}: State of tables to get. 
         * (0:All, 1: 空闲, 2: 正在使用, 并且未结账, 3: 正在使用并且已结帐, 4: 被预订)
         */
        public DataSet getTables(int tableType, int state)
        {
            SqlConnection con = null;
            SqlDataReader sdr = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();

                // create a sql command object
                string strSql = "SELECT tableName,tableType, tableState, tablePrice, notes, num FROM tb_table " +
                    ((tableType > 0) ? "WHERE tableType=" + tableType : "");

                SqlDataAdapter sda = new SqlDataAdapter(strSql, con);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                sda.Dispose();
                return ds;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (sdr != null) { sdr.Close(); sdr.Dispose(); sdr = null; }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        /*
         * Get basic information of one table by tableName.
         * @return {Hashtable} Save all available information
         */
        public Object getTableBasicInfo(string tableName)
        {
            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();
                string strSql = "select tableType, tablePrice, num from tb_table" +
                            " where tableName='" + tableName + "';";
                SqlCommand command = new SqlCommand(strSql, con);
                SqlDataReader sdr = command.ExecuteReader(CommandBehavior.CloseConnection);
                Hashtable result = new Hashtable();
                if (sdr.Read())
                {
                    result.Add("tableType", sdr["tableType"].ToString().Trim());
                    result.Add("tablePrice", sdr["tablePrice"].ToString().Trim());
                    result.Add("num", sdr["num"].ToString().Trim());
                }
                return result;
            }
            catch (Exception e) { throw e; }
            finally { if (con != null) { con.Close(); con.Dispose(); con = null; } };
        }

        public Object getTableAdvInfo(string tableName)
        {
            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();
                string strSql = String.Format("select orderTime, orderID, waiterID, totalPrice "
                                +"from tb_orderlist "
                                +"where tableName='{0}' and orderState in({1}, {2}, {3});",
                                tableName,
                                OrderDAO.ORDER_STATE_ORDERED,
                                OrderDAO.ORDER_STATE_ORDERING,
                                OrderDAO.ORDER_STATE_PAID
                            );
                SqlCommand command = new SqlCommand(strSql, con);
                SqlDataReader sdr = command.ExecuteReader(CommandBehavior.CloseConnection);
                Hashtable result = new Hashtable();
                if (sdr.Read())
                {
                    result.Add("orderTime", sdr["orderTime"].ToString().Trim());
                    result.Add("orderID", sdr["orderID"].ToString().Trim());
                    result.Add("waiterID", sdr["waiterID"].ToString().Trim());
                    result.Add("totalPrice", sdr["totalPrice"].ToString().Trim());
                }
                return result;
            }
            catch (Exception e) { throw e; }
            finally { if (con != null) { con.Close(); con.Dispose(); con = null; } };
        }

        /**
         * Get one table's status by tableName.
         */
        public int getOneTableState(string strTableName)
        {
            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();
                string strSql = "select tableState from tb_table where tableName='"+strTableName+"';";
                SqlCommand command = new SqlCommand(strSql, con);
                SqlDataReader sdr = command.ExecuteReader(CommandBehavior.CloseConnection);
                if (sdr.Read())
                {
                    return Int32.Parse(sdr["tableState"].ToString());
                }
                return UTIL.TABLE.STATUS_NOTUSE;
            }
            catch (Exception e) { throw e; }
            finally { if (con != null) { con.Close(); con.Dispose(); con = null; } };
        }

        public void setTableState(string strTableName, int state)
        {
            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();
                string strSql = string.Format("update tb_table set tableState={0} where tableName='{1}';",
                                                        state, strTableName);
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.ExecuteNonQuery();
            }
            catch (Exception e) { throw e; }
            finally { if (con != null) { con.Close(); con.Dispose(); con = null; } };
        }

        /**
         * Add new table.
         * @param {string} strOperName The name of operator
         * @param {string} strTableName The id of one table's name
         * @param {int} tableType Four type of one table from 1 to 4
         * @param {double} tablePrice The value of one table to cost
         * @param {int} tableState Four state of one table from 1 to 4
         */
        public void addNewTable(string strOperName, string strTableName, int tableType, double tablePrice, string notes, int tableState)
        {
            // Judge whether tableState, tableType and tablePrice is available
            if (tableState < 1 || tableState > 4 || tableType > 4 || tableType < 1 || tablePrice < 0)
                throw new Exception("RMS ERROR: Not available attribute for adding a new table.");

            if (this.isTableNameExist(strTableName))
                throw new Exception("RMS ERROR: Add new table error. This table name is exist.");

            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();

                string strSql = String.Format("insert into tb_table(tableName, tableType, tablePrice, notes, tableState) "+
                                                "values('{0}', {1}, {2}, '{3}', {4});",
                                                        strTableName,
                                                        tableType,
                                                        tablePrice,
                                                        notes,
                                                        tableState
                                                );
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.ExecuteNonQuery();

                // Save Result in Log
                RMS.Logger.Add(strOperName, "添加桌位: " + strTableName+"成功", RMS.Logger.INFO);
            }
            catch (Exception e) {
                // Save Result in Log
                string strDetails = "添加桌位: " + strTableName + "失败, 错误日志: "+e.Message;
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.ERROR);
                throw e;
            }
            finally { if (con != null) { con.Close(); con.Dispose(); con = null; } };
        }

        /**
         * Delete one table from db.
         */
        public void deleteOldTable(string strOperName, string strTableName)
        {
            // Judge whether this table is exist
            if (!isTableNameExist(strTableName))
                throw new Exception("RMS ERROR: TableName of "+strTableName+" don't exist in db.");

            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();
                string strSql = String.Format("delete from tb_table where tableName='{0}';", strTableName);
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.ExecuteNonQuery();

                // Save Result in Log
                RMS.Logger.Add(strOperName, "删除桌位: " + strTableName + "成功", RMS.Logger.INFO);
            }
            catch (Exception e)
            {
                // Save Result in Log
                string strDetails = "删除桌位: " + strTableName + "失败, 错误日志: " + e.Message;
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.ERROR);
                throw e;
            }
            finally { if (con != null) { con.Close(); con.Dispose(); con = null; } };
        }

        public void updateTable(string strOperName, string strTableName, int tableType, double tablePrice, string notes)
        {
            // Judge whether tableState, tableType and tablePrice is available
            if (tableType > 4 || tableType < 1 || tablePrice < 0)
                throw new Exception("RMS ERROR: Not available attribute for adding a new table.");

            if (!this.isTableNameExist(strTableName))
                throw new Exception("RMS ERROR: Update table error. This table name is not exist.");

            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();

                string strSql = String.Format("update tb_table set tableType={0}, tablePrice={1}, notes='{2}' " +
                                                "WHERE tableName='{3}';",
                                                        tableType,
                                                        tablePrice,
                                                        notes,
                                                        strTableName
                                                );
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.ExecuteNonQuery();

                // Save Result in Log
                RMS.Logger.Add(strOperName, "修改桌位: " + strTableName + "成功", RMS.Logger.INFO);
            }
            catch (Exception e)
            {
                // Save Result in Log
                string strDetails = "修改桌位: " + strTableName + "失败, 错误日志: " + e.Message;
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.ERROR);
                throw e;
            }
            finally { if (con != null) { con.Close(); con.Dispose(); con = null; } };
        }

        /* Judge whether one tablename exist in db. */
        public bool isTableNameExist(string strTableName)
        {
            SqlConnection con = null;
            SqlDataReader sdr = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();
                string strSql = String.Format("select count(id) from tb_table where tableName='{0}' group by tableName;", strTableName);
                SqlCommand cmd = new SqlCommand(strSql, con);
                sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (sdr.Read())
                {
                    return (Int32.Parse(sdr[0].ToString()) == 1);
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e) { throw e; }
            finally { if (con != null) { con.Close(); con.Dispose(); con = null; } };
        }

        public static string getTableStateString(int iState)
        {
            switch (iState)
            {
                case DataAccess.TableDAO.STATE_AVAILABLE:
                    return "空闲";
                case DataAccess.TableDAO.STATE_ORDERING:
                    return "点菜中";
                case DataAccess.TableDAO.STATE_ORDERED:
                    return "点菜完成";
                case DataAccess.TableDAO.STATE_PAID:
                    return "已结帐";
                default:
                    return "";
            }
        } // end of function

        public static string getTableTypeString(int iType)
        {
            switch (iType)
            {
                case DataAccess.TableDAO.TYPE_DT:
                    return "大厅";
                case DataAccess.TableDAO.TYPE_PTBX:
                    return "普通包厢";
                case DataAccess.TableDAO.TYPE_GJBX:
                    return "高级包厢";
                case DataAccess.TableDAO.TYPE_VIPBX:
                    return "VIP包厢";
                default:
                    return "";
            }
        } // end of function

        public static string getStringMoney(double money)
        {
            if (money > 0d)
                return "￥" + string.Format("{0:.00}", money) + "";
            else
                return "￥0.00";
        }
    }
}
